<?php

namespace xhadmin;
use think\facade\Validate;
use think\facade\Cache;
use think\facade\Log; 
use PhpOffice\PhpSpreadsheet\IOFactory;

class CommonService
{
	
	 /**
     * 验证数据
     * @access protected
     * @param  array        $data     数据
     * @param  string|array $validate 验证器名或者验证规则数组
     * @return array|string|true
     */
	public static function validate($rule,$data,$msg=[]){
		$validate = Validate::rule($rule)->message($msg);	
		if (!$validate->check($data)) {
			throw new \Exception($validate->getError());
		}
		return true;
	}
	
	 /**
     * 过滤掉空的数组
     * @access protected
     * @param  array        $data     数据
     * @return array
     */
	public static function filterEmptyArray($data = []){
		foreach( $data as $k=>$v){   
			if( !$v && $v !== 0)   
				unset( $data[$k] );   
		}
		return $data;
	}
	
	
	/**
     * 生成sql查询语句
     * @access protected
     * @param  sql     原始sql语句
     * @param  $where  查询条件
	 * @param  $limit  分页
	 * @param  $orderby  排序
     * @return array
     */
	/**
     * 生成sql查询语句
     * @access protected
     * @param  sql     原始sql语句
     * @param  $where  查询条件
	 * @param  $limit  分页
	 * @param  $orderby  排序
     * @return array
     */
	protected static function loadList($sql,$where=[],$limit,$orderby){
		$sql = strtolower($sql);
		$map = '';
		foreach($where as $key=>$val){
			if(is_array($val)){
				if(stripos($sql,'join') > 0){
					if($val[1] == 'between'){
						list($start,$end) = explode(',',$val[2]);
						$map .= 'a.'.$val[0].' between '.$start.' and '.$end.' and ';
					}else{
						$map .= 'a.'.$val[0].' '.$val[1]." '".$val[2]."'".' and ';
					}
				}else{
					if($val[1] == 'between'){
						list($start,$end) = explode(',',$val[2]);
						$map .= $val[0].' between '.$start.' and '.$end.' and ';
					}else{
						$map .= $val[0].$val[1]."'".$val[2]."'".' and ';
					}
					
				}		
			}
		}
		$map .= '1=1';
		$is_where = strripos($sql,"where");
		if($is_where === false){
            $where = !empty($where) ?  ' where '.$map : '';
            $sql = $sql.$where;
		}else{
		    $l_sql = substr($sql, 0, $is_where);
		    $r_sql = substr($sql, $is_where+5, strlen($sql)- $is_where - 5);
            $where = !empty($where) ?  ' where '.$map.' and ' : ' where ';
            $sql = $l_sql . $where . $r_sql;
		}
		if(stripos($sql,'join') > 0){
			$orderby = ' order by a.'.$orderby;
		}else{
			$orderby = ' order by '.$orderby;
		}
		
		$limit = ' limit '.$limit;
		
		$countWhere = preg_replace('/^select(.*) from/iUs','select count(*) as count from',$sql);
		if (strripos($sql,"order by")=== false) {
            $sql .= $orderby;
        }
        if (strripos($sql,"limit")=== false) {
            $sql .= $limit;
        }
		$result = db()->query($sql);
		$count = db()->query($countWhere);
		return ['list'=>$result,'count'=>$count[0]['count']];
	}
	
	
	//时间区间筛选组合
	public static function getTimeWhere($startTime,$endTime){
		
		$where = [];
		if(!empty($startTime) && !empty($endTime)){
			$where = ['between',strtotime($startTime).','.strtotime($endTime)];
		}
		if(!empty($startTime) && empty($endTime)){
			$where = ['>',strtotime($startTime)];
		}
		if(empty($startTime) && !empty($endTime)){
			$where = ['<',strtotime($endTime)];
		}
		return $where;
	}
	
	//数字区间筛选组合
	public static function getNumWhere($start,$end){
		
		$where = [];
		if(!empty($start) && !empty($end)){
			$where = ['between',$start.','.$end];
		}
		if(!empty($start) && empty($end)){
			$where = ['>',$start];
		}
		if(empty($start) && !empty($end)){
			$where = ['<',$end];
		}
		return $where;
	}
	
	
	
	//交易流水
	//data['pay_type']  1微信支付 2支付宝支付
	public static function setTranslation($data){
		
		if($data['pay_type'] == 1){
			$dt['total_fee']	= $data['total_fee']/100;
			$dt['third_trans_id'] 	= $data['transaction_id'];
		}else{
			$dt['total_fee']	= $data['total_amount'];  
			$dt['third_trans_id'] 	= $data['trade_no'];
			$dt['uid']			= $data['body'];
		}
		
		$dt['order_id']			= $data['out_trade_no'];
		$dt['pay_type']			= $data['pay_type'];
		$dt['trans_type'] 		= $data['trans_type'];
		$dt['create_time'] 		= time();
		
		\xhadmin\db\Translation::createData($dt);
	}

	
	//导入excel数据
	public static function importData($key){
		$file = explode('.', $_FILES['file_name']['name']);
		if (!in_array(end($file), array('xls','xlsx','csv'))) {
			throw new \Exception('请选择xls文件！');
			exit;
		}
		$path = $_FILES['file_name']['tmp_name'];
		if (empty($path)) {
			throw new \Exception('请选择要上传的文件！');
			exit;
		}

		set_time_limit(0);

		$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
		$reader->setReadDataOnly(true);
		$spreadsheet = $reader->load($path); //载入excel表格
		$worksheet = $spreadsheet->getActiveSheet();
		
		$sheet = $spreadsheet->getActiveSheet();
		$res = [];

		foreach ($sheet->getRowIterator(1) as $row) {
			$tmp = [];
			foreach ($row->getCellIterator() as $cell) {
				$tmp[] = $cell->getFormattedValue();
			}
			
			if(self::filterEmptyArray($tmp)){
				$res[$row->getRowIndex()] = $tmp;
			}
		}
		
		return $res;
	}
	
	
	
	//导出excel表头设置
	public function getTag($key3,$no=100){
		$data=[];
		$key = ord("A");//A--65
		$key2 = ord("@");//@--64	
		for($n=1;$n<=$no;$n++){
			if($key>ord("Z")){
				$key2 += 1;
				$key = ord("A");
				$data[$n] = chr($key2).chr($key);//超过26个字母时才会启用  
			}else{
				if($key2>=ord("A")){
					$data[$n] = chr($key2).chr($key);//超过26个字母时才会启用  
				}else{
					$data[$n] = chr($key);
				}
			}
			$key += 1;
		}
		return $data[$key3];
    }

	
    
}
 